{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Alvald1/HSE_colab/blob/main/17_SQLite.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "804Y3LGX5sGl"
      },
      "source": [
        "# Использование СУБД SQLite\n",
        "\n",
        "SQLite - компактная встраиваемая СУБД. SQLite хранит всю базу данных (включая определения, таблицы, индексы и данные) в единственном стандартном файле на том компьютере, на котором исполняется программа. https://ru.wikipedia.org/wiki/SQLite"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o6ipiWpw5sGt"
      },
      "outputs": [],
      "source": [
        "import sqlite3\n",
        " \n",
        "conn = sqlite3.connect(\"mydatabase.db\") # или :memory: чтобы сохранить в RAM\n",
        "cursor = conn.cursor()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1DkdU5ZK5sGu"
      },
      "source": [
        "SQL — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных. https://ru.wikipedia.org/wiki/SQL\n",
        "\n",
        "Основной набор операций:\n",
        "* создание в базе данных новой таблицы;\n",
        "* добавление в таблицу новых записей;\n",
        "* изменение записей;\n",
        "* удаление записей;\n",
        "* выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);\n",
        "* изменение структур таблиц."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_mvuP1mZ5sGu"
      },
      "source": [
        "### Создание новой таблицы"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xi3_XH-c5sGu",
        "outputId": "7d0b09d6-c63a-466d-8f7a-b681174ab401"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x1cfdae74420>"
            ]
          },
          "execution_count": 2,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import sqlite3\n",
        " \n",
        "conn = sqlite3.connect(\"mydatabase.db\") # или :memory: чтобы сохранить в RAM\n",
        "cursor = conn.cursor()\n",
        "# Создание таблицы\n",
        "cursor.execute(\"\"\"CREATE TABLE albums\n",
        "                  (artist, title text, label text,\n",
        "                   units_mln int, year int)\n",
        "               \"\"\")\n",
        "\n",
        "# cursor.execute(\"\"\"DROP TABLE albums\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b3BYAkWY5sGv"
      },
      "source": [
        "### Удаление таблицы "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Dic2gRnA5sGv"
      },
      "outputs": [],
      "source": [
        "cursor.execute(\"\"\"DROP TABLE albums\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wdWNRuPF5sGv",
        "outputId": "2cfce75d-ae58-4d9f-969b-be008c7955ac"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x1cfdae74420>"
            ]
          },
          "execution_count": 3,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cursor.execute(\"\"\"DROP TABLE IF EXISTS albums\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rX3fMDwU5sGw",
        "outputId": "58d34859-4a58-4911-e70c-eb32d6116a95"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x1cfdae74420>"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cursor.execute(\"\"\"CREATE TABLE albums\n",
        "                  (id INTEGER PRIMARY KEY, artist, title text, label text,\n",
        "                   units_mln int, year int)\n",
        "               \"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p0N56OCa5sGw"
      },
      "source": [
        "### Добавление данных"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wIzmFIty5sGw"
      },
      "outputs": [],
      "source": [
        "# Вставляем данные в таблицу\n",
        "cursor.execute(\n",
        "    \"\"\"INSERT INTO albums(artist, title, label, units_mln, year) VALUES (?, ?, ?, ?, ?)\"\"\", \n",
        "    ('Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)\n",
        ")# Безопасный метод \"?\"\n",
        "\n",
        "# Сохраняем изменения\n",
        "conn.commit()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WjB5rIwt5sGx"
      },
      "outputs": [],
      "source": [
        "# Вставляем множество данных в таблицу используя безопасный метод \"?\"\n",
        "albums = [('Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)]\n",
        " \n",
        "cursor.executemany(\n",
        "    \"INSERT INTO albums(artist, title, label, units_mln, year) VALUES (?,?,?,?,?)\", \n",
        "    albums\n",
        ")\n",
        "conn.commit()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RYULBDc25sGx"
      },
      "source": [
        "### Выборка данных"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gAaOZHlh5sGx",
        "outputId": "8bf9a3fe-b678-4bf8-9066-8908f19a64ed"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "(1, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)\n",
            "(2, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)\n",
            "(3, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)\n",
            "(4, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)\n"
          ]
        }
      ],
      "source": [
        "for row in cursor.execute(\"SELECT * FROM albums\"):\n",
        "    print(row)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2ES4XpmB5sGx"
      },
      "source": [
        "### Изменение записи"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "P41zB13Q5sGy",
        "outputId": "458bf6c8-19d8-4dfe-ccaa-75b9839c05f5"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x1cfdae74420>"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cursor.execute(\"UPDATE albums SET year=? WHERE id=?\",(1970, 3))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "znET5-ek5sGy",
        "outputId": "ad2abe29-17bd-4c64-88b9-d69c9099456e"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "[(1, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979),\n",
              " (2, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979),\n",
              " (3, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1970),\n",
              " (4, 'Pink Floyd', 'The Wall', 'Columbia Records', 23, 1979)]"
            ]
          },
          "execution_count": 9,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Получение списка значений\n",
        "cursor.execute(\"SELECT * FROM albums\").fetchall()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cQJDZo0c5sGy"
      },
      "outputs": [],
      "source": [
        "sql = \"\"\"\n",
        "UPDATE albums \n",
        "SET artist = 'John Doe' \n",
        "WHERE artist = 'Pink Floyd'\n",
        "\"\"\"\n",
        "\n",
        "cursor.execute(sql)\n",
        "conn.commit()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HvsWyH7K5sGy",
        "outputId": "57c44b12-ad92-4acb-8f20-f47b0641a7ff"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "[(1, 'John Doe', 'The Wall', 'Columbia Records', 23, 1979),\n",
              " (2, 'John Doe', 'The Wall', 'Columbia Records', 23, 1979),\n",
              " (3, 'John Doe', 'The Wall', 'Columbia Records', 23, 1970)]"
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cursor.execute(\"SELECT * FROM albums\").fetchall()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cy6G-wSc5sGz"
      },
      "source": [
        "### Удаление записи"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nXwjQ5495sGz"
      },
      "outputs": [],
      "source": [
        "sql = \"DELETE FROM albums WHERE id = ?\"\n",
        " \n",
        "cursor.execute(sql, (4,))\n",
        "conn.commit()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1JNgojD55sGz",
        "outputId": "f10dc26a-6537-4d66-eeff-588d930ff015"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x1cfdae74420>"
            ]
          },
          "execution_count": 16,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cursor.execute(\"\"\"DROP TABLE albums\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_x3pIrbF5sGz"
      },
      "source": [
        "## Экспорт данных из DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NdI1n5215sG0"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "data = pd.read_excel('17_art.xls', index_col='Unnamed: 0')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YxZbNEnc5sG0",
        "outputId": "8046acb1-45cb-4498-bdac-7130476c56c9"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>artist</th>\n",
              "      <th>title</th>\n",
              "      <th>label</th>\n",
              "      <th>units_mln</th>\n",
              "      <th>year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>Phil Collins «No Jacket Require</td>\n",
              "      <td>Phil Collins «No Jacket Require</td>\n",
              "      <td>Atlantic Records</td>\n",
              "      <td>12</td>\n",
              "      <td>1985</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>Matchbox Twenty «Yourself or Someone Like Yo</td>\n",
              "      <td>Matchbox Twenty «Yourself or Someone Like Yo</td>\n",
              "      <td>Atlantic Records</td>\n",
              "      <td>12</td>\n",
              "      <td>1996</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>Led Zeppelin «Led Zeppelin I</td>\n",
              "      <td>Led Zeppelin «Led Zeppelin I</td>\n",
              "      <td>Atlantic Records</td>\n",
              "      <td>12</td>\n",
              "      <td>1969</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>Kenny Rogers «Kenny Rogers’ Greatest Hit</td>\n",
              "      <td>Kenny Rogers «Kenny Rogers’ Greatest Hit</td>\n",
              "      <td>Atlantic Records</td>\n",
              "      <td>12</td>\n",
              "      <td>1980</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>Kenny G «Breathles</td>\n",
              "      <td>Kenny G «Breathles</td>\n",
              "      <td>Arista Records</td>\n",
              "      <td>12</td>\n",
              "      <td>1991</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                         artist  \\\n",
              "0               Phil Collins «No Jacket Require   \n",
              "1  Matchbox Twenty «Yourself or Someone Like Yo   \n",
              "2                  Led Zeppelin «Led Zeppelin I   \n",
              "3      Kenny Rogers «Kenny Rogers’ Greatest Hit   \n",
              "4                            Kenny G «Breathles   \n",
              "\n",
              "                                          title             label  units_mln  \\\n",
              "0               Phil Collins «No Jacket Require  Atlantic Records         12   \n",
              "1  Matchbox Twenty «Yourself or Someone Like Yo  Atlantic Records         12   \n",
              "2                  Led Zeppelin «Led Zeppelin I  Atlantic Records         12   \n",
              "3      Kenny Rogers «Kenny Rogers’ Greatest Hit  Atlantic Records         12   \n",
              "4                            Kenny G «Breathles    Arista Records         12   \n",
              "\n",
              "   year  \n",
              "0  1985  \n",
              "1  1996  \n",
              "2  1969  \n",
              "3  1980  \n",
              "4  1991  "
            ]
          },
          "execution_count": 19,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "data.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iUlFHuEb5sG0"
      },
      "outputs": [],
      "source": [
        "data.to_sql('albums',conn)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LKhMujUG5sG0"
      },
      "outputs": [],
      "source": [
        "SQLres = cursor.execute(\"\"\"\n",
        "SELECT * FROM albums \n",
        "WHERE `year` > 1980 \n",
        "AND units_mln <33\n",
        "ORDER BY units_mln DESC \n",
        "LIMIT 0, 5\"\"\" )\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5WTTbeLJ5sG0",
        "outputId": "6a0861a1-81ce-4e1c-ead7-e5472df7a683"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "(48, 'Eagles «Their Greatest Hits 1971-197', 'agles «Their Greatest Hits 1971-197', 'Rhino', 29, 1986)\n",
            "(47, 'Billy Joel «Greatest Hits Volume I\\xa0& Volume I', 'illy Joel «Greatest Hits Volume I\\xa0& Volume I', 'Columbia Records', 23, 1985)\n",
            "(43, 'Garth Brooks «Double Liv', 'arth Brooks «Double Liv', 'Capitol Nashville', 21, 1998)\n",
            "(41, 'Shania Twain «Come on\\xa0Ove', 'hania Twain «Come on\\xa0Ove', 'Mercury Nashville', 20, 1987)\n",
            "(42, 'Fleetwood Mac «Rumour', 'leetwood Mac «Rumour', 'Warner Bros.', 20, 1987)\n"
          ]
        }
      ],
      "source": [
        "for row in SQLres:\n",
        "    print(row)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rFYEmkCq5sG1",
        "outputId": "aeabdce2-6ae1-46c8-cafb-f4af9d8128d0"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "(49, 'Michael Jackson «Thrille', 'ichael Jackson «Thrille', 'Epic Records', 33, 1982)\n",
            "(48, 'Eagles «Their Greatest Hits 1971-197', 'agles «Their Greatest Hits 1971-197', 'Rhino', 29, 1986)\n",
            "(45, 'Pink Floyd «The Wal', 'ink Floyd «The Wal', 'Columbia Records', 23, 1979)\n",
            "(47, 'Billy Joel «Greatest Hits Volume I\\xa0& Volume I', 'illy Joel «Greatest Hits Volume I\\xa0& Volume I', 'Columbia Records', 23, 1985)\n",
            "(44, 'AC/DC «Back in\\xa0Blac', 'C/DC «Back in\\xa0Blac', 'Epic Records', 22, 1980)\n",
            "(43, 'Garth Brooks «Double Liv', 'arth Brooks «Double Liv', 'Capitol Nashville', 21, 1998)\n",
            "(41, 'Shania Twain «Come on\\xa0Ove', 'hania Twain «Come on\\xa0Ove', 'Mercury Nashville', 20, 1987)\n",
            "(42, 'Fleetwood Mac «Rumour', 'leetwood Mac «Rumour', 'Warner Bros.', 20, 1987)\n",
            "(38, 'Whitney Houston «The Bodyguar', 'Whitney Houston «The Bodyguar', 'RCA', 18, 1992)\n",
            "(39, 'Guns N’ Roses «Appetite for Destructio', 'Guns N’ Roses «Appetite for Destructio', 'Geffen Records', 18, 1987)\n"
          ]
        }
      ],
      "source": [
        "for row in cursor.execute(\"\"\"\n",
        "SELECT * FROM `albums` \n",
        "WHERE `year` > 1976  \n",
        "ORDER BY `units_mln` DESC LIMIT 0, 10\"\"\"):\n",
        "    print(row)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "udbx8sLP5sG1"
      },
      "source": [
        "## Задачи для самостоятельного выполнения"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E6Q-k9PA5sG1"
      },
      "source": [
        "**Самый самый**. По полученной БД, выяснить:\n",
        "1. Самый свежий альбом\n",
        "1. Сколько альбомов было выпущено в 1987 году.\n",
        "1. Кто выпустил самый успешный альбом до 1987 года включительно? После 1987г?\n",
        "1. Сколько всего копий альбомов 1987 года было продано?\n",
        "1. Какой исполнитель выпустил больше всего альбомов?\n",
        "1. В каком году было выпущено больше всего альбомов?\n",
        "1. Альбомы какого года продавались лучше?"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Настройка"
      ],
      "metadata": {
        "id": "JRLLfGZHSx-m"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "data = pd.read_excel('17_art.xls', index_col='Unnamed: 0')"
      ],
      "metadata": {
        "id": "CwPoxcbw5wdb"
      },
      "execution_count": 85,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        " \n",
        "conn = sqlite3.connect(\"mydatabase.db\")\n",
        "cursor = conn.cursor()"
      ],
      "metadata": {
        "id": "MkEnpq4W6z8y"
      },
      "execution_count": 86,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "data.to_sql('albums',conn, if_exists='replace')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y4IL6xQF63wz",
        "outputId": "7b3770de-63ab-4a55-bfcc-e8c788c665a0"
      },
      "execution_count": 88,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "50"
            ]
          },
          "metadata": {},
          "execution_count": 88
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 1"
      ],
      "metadata": {
        "id": "DF9igxE9RqvO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"\n",
        "SELECT title, max(year) FROM albums\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "Ae__szckRHo-"
      },
      "execution_count": 89,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Самый свежий альбом: ',SQLres[0])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YQGtzjLh7EXL",
        "outputId": "17c928b9-4a36-4021-e081-ddd675a9ac96"
      },
      "execution_count": 92,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Самый свежий альбом:  Adele «2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 2"
      ],
      "metadata": {
        "id": "zNnwKIWGMo2O"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"\n",
        "SELECT count(*) FROM albums \n",
        "WHERE year=1987\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "OvNcADvy_qHU"
      },
      "execution_count": 108,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('В 1987 году было выпущенно',SQLres[0], 'альбома')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SQxvqSN3_3eU",
        "outputId": "afbb6006-eab5-40f7-e7c5-5c358507a23a"
      },
      "execution_count": 109,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "В 1987 году было выпущенно 4 альбома\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 3.1"
      ],
      "metadata": {
        "id": "F99gCf1PSkVm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"\n",
        "SELECT title, max(units_mln) FROM albums \n",
        "WHERE year<=1987\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "GeZEtZmpAOt1"
      },
      "execution_count": 95,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Выпустил самый успешный альбом до 1987 года включительно: ',SQLres[0])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iIzL7iYfAoas",
        "outputId": "50ea03bf-070b-4789-bc57-e8b36598ca58"
      },
      "execution_count": 96,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Выпустил самый успешный альбом до 1987 года включительно:  ichael Jackson «Thrille\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 3.2"
      ],
      "metadata": {
        "id": "pLXpZ1qsSmue"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"\n",
        "SELECT title, max(units_mln) FROM albums \n",
        "WHERE year>1987\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "REviVSDrAwSV"
      },
      "execution_count": 97,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Выпустил самый успешный альбом после 1987 года: ',SQLres[0])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vzM1SwrdAzpj",
        "outputId": "d80b6d7a-e4ab-4ec1-efc7-273d798fe683"
      },
      "execution_count": 99,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Выпустил самый успешный альбом после 1987 года:  arth Brooks «Double Liv\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 4"
      ],
      "metadata": {
        "id": "rbT6YduhSoR9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"\n",
        "SELECT sum(units_mln) FROM albums \n",
        "WHERE year=1987\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "lmsGDponA8ns"
      },
      "execution_count": 100,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Всего копий альбомов 1987 года было продано: ', SQLres[0])"
      ],
      "metadata": {
        "id": "-MbQctPXA82s",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e6267010-17ef-411f-d809-77d57d499cc7"
      },
      "execution_count": 101,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Всего копий альбомов 1987 года было продано:  70\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 5"
      ],
      "metadata": {
        "id": "8_me10pBSpRu"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Невозможно определить какой исполнитель выпустил больше всех альбомов, потому что в бд столбецы 'artist' и 'title' одинаковые. "
      ],
      "metadata": {
        "id": "zEavaFwpE-W0"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 6"
      ],
      "metadata": {
        "id": "AmeTVaejSqgl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"SELECT year, max(cnt) FROM (SELECT year, count(title) as cnt FROM albums GROUP BY year)\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "w03KvMIeLwe9"
      },
      "execution_count": 102,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Выпущено больше всего альбомов в', SQLres[0])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0FH3ZxnhL2td",
        "outputId": "96eb0c7a-9e89-4ab5-b0bd-01e9f6295bf9"
      },
      "execution_count": 104,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Выпущено больше всего альбомов в 1987\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Задание 7"
      ],
      "metadata": {
        "id": "o4xYQD2eSraO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"\"\"SELECT year, max(sum) FROM (SELECT year, sum(units_mln) as sum FROM albums GROUP BY year)\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()"
      ],
      "metadata": {
        "id": "J2nOPxHcMZPl"
      },
      "execution_count": 105,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print('Альбомы',SQLres[0],'года продавались лучше остальных')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9EIfU6lLMYyF",
        "outputId": "2efffc9a-6698-4588-c8de-0892a078f5db"
      },
      "execution_count": 106,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Альбомы 1987 года продавались лучше остальных\n"
          ]
        }
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.4"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}